﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using Task_Network_Center.General;

namespace Task_Network_Center.Reports
{
    class ReportBuilder
    {

        MySqlConnection conn = null;
        public void PrepareTable(DataView table)
        {
            String query = "INSERT INTO TableReport (id_tache,projets,description,inscrit_par,date_dinscription,"
        + "solution,executant,duree,progression,avancement,equipe,priorite,echeance,departement,complete_le,complete_par,note1,"
        + "note2,note3,note4) VALUE (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20)";
            clearTable();
            foreach (DataRowView row in table)
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                MySqlCommand cmd = new MySqlCommand(query, conn);

                try
                {


                    conn.Open();
                    cmd.Parameters.AddWithValue("p1", Convert.ToInt32(row[0]));
                    cmd.Parameters.AddWithValue("p2", row[1].ToString());
                    cmd.Parameters.AddWithValue("p3", row[2].ToString());
                    cmd.Parameters.AddWithValue("p4", row[3].ToString());
                    cmd.Parameters.AddWithValue("p5", row[4].ToString());
                    cmd.Parameters.AddWithValue("p6", row[5].ToString());
                    cmd.Parameters.AddWithValue("p7", row[6].ToString());
                    cmd.Parameters.AddWithValue("p8", row[7].ToString());
                    cmd.Parameters.AddWithValue("p9", row[8].ToString());
                    cmd.Parameters.AddWithValue("p10", row[9].ToString());
                    cmd.Parameters.AddWithValue("p11", row[10].ToString());
                    cmd.Parameters.AddWithValue("p12", row[11].ToString());
                    if (row[12].ToString() != "")
                    {
                        cmd.Parameters.AddWithValue("p13", row[12].ToString());  
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("p13", DBNull.Value);
                    }

                    cmd.Parameters.AddWithValue("p14", row[13].ToString());

                    if (row[14].ToString() != "")
                    {
                        cmd.Parameters.AddWithValue("p15",row[14].ToString());   
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("p15", DBNull.Value);
                    }
                    
                    cmd.Parameters.AddWithValue("p16", row[15].ToString());
                    cmd.Parameters.AddWithValue("p17", row[16].ToString());
                    cmd.Parameters.AddWithValue("p18", row[17].ToString());
                    cmd.Parameters.AddWithValue("p19", row[18].ToString());
                    cmd.Parameters.AddWithValue("p20", row[19].ToString());
                    cmd.ExecuteNonQuery();
                    conn.Close();


                }
                catch (Exception)
                {

                    throw;
                }
            }
        }


        private void clearTable()
        {

            String query = "TRUNCATE TABLE TableReport";
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception)
            {
                
                throw;
            }
        }
    }
}
